﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;

namespace WPFExcelReport.Helpers
{
    public class SQLHelper
    {
        public static string connString = "";
        public static SqlConnection connect;
        public static SqlCommand cmd;

        public static object GetSingleResult(string sql)
        {

            try
            {
                if (connect.State == ConnectionState.Closed)
                {
                    connect.Open();
                }
                cmd.Connection = connect;
                SqlParameter param = new SqlParameter();
                cmd.Parameters.Add(param);
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connect.Close();
            }
        }
        public static SqlDataReader GetReader(string sql)
        {
            try
            {
                if (connect.State == ConnectionState.Closed)
                {
                    connect.Open();
                }
                cmd.Connection = connect;
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            { 
                throw ex;
            }
            finally
            {
                connect.Close();
            }
        }
        public static void SetUpSQLServer(Dictionary<string, object> dicSrcParamater)
        {

            string conn = (string)dicSrcParamater["ConnectionString"];
            cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            if (connString.Trim() == conn.Trim())
            {

                return;
            }


            foreach (KeyValuePair<string, object> kvp in dicSrcParamater)
            {
                switch (kvp.Key)
                {
                    case "DBTYPE":
                    case "AGENTID":
                    case "CommandText":
                    case "CommandType":
                    default:
                        break;
                    case "ConnectionString":
                        conn = (string)dicSrcParamater["ConnectionString"];
                        connString = conn;
                        connect = new SqlConnection(connString);
                        break;
                }
            }
        }
        public static DataSet GetData(Dictionary<string, object> dicSrcParamater)
        {
            DataSet dsResult = new DataSet();
            string dbType = "Oracle";
            if (dicSrcParamater.ContainsKey("DBTYPE"))
            {
                dbType = dicSrcParamater["DBTYPE"].ToString();
            }
            if (dbType != "SQLSERVER")
                return dsResult;
            SetUpSQLServer(dicSrcParamater);

            try
            {
                string tb = "Table";
                string strCommandType = "";
                foreach (KeyValuePair<string, object> kvp in dicSrcParamater)
                {
                    switch (kvp.Key)
                    {
                        case "DBTYPE":
                        case "AGENTID":
                        case "ConnectionString":
                            break;
                        case "DataTable":
                            tb = (string)dicSrcParamater["DataTable"];
                            break;
                        case "CommandType":
                            strCommandType = (string)dicSrcParamater["CommandType"];
                            switch (strCommandType)
                            {
                                case "StoredProcedure":
                                    cmd.CommandType = CommandType.StoredProcedure;
                                    break;
                                case "TableDirect":
                                    SQLHelper.cmd.CommandType = CommandType.TableDirect;
                                    break;
                                default:
                                    cmd.CommandType = CommandType.Text;
                                    break;
                            }
                            break;
                        case "CommandText":
                            cmd.CommandText = (string)dicSrcParamater["CommandText"];
                            break;
                        default:
                            SqlParameter sqlParameter = new SqlParameter();
                            sqlParameter.ParameterName = kvp.Key;
                            sqlParameter.Value = (string)kvp.Value;
                            SQLHelper.cmd.Parameters.Add(sqlParameter);
                            break;
                    }
                }
                if (connect.State == ConnectionState.Open)
                {
                    connect.Close();
                }

                if (connect.State == ConnectionState.Closed)
                {
                    connect.Open();
                }

                cmd.Connection = connect;
                SqlDataReader objRead = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                DataTable dt = new DataTable(); 
                dt.Load(objRead);
                dsResult.Merge(dt);
                dsResult.Tables[0].TableName = "Table";
                return dsResult;
            }
            catch (Exception ex)
            {

                LogHelper.error(ex.StackTrace+ex);
            }
            finally
            {
                connect.Close();
            }
        }
    }
}
